package org.bzjj.sql;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;

import org.apache.log4j.Logger;

import org.bzjj.log.MyLog;

public class Sql {
	private static String keyStr = "`";
	private static String valueStr = "'";
	private static String splitStr = ",";
	private static String equalStr = "=";
	private static String andStr = " AND ";
	private static String leftBracket = "(";
	private static String rightBracket = ")";

	private static String singleSelect = " LIMIT 0, 1";

	/**
	 * 得到形如`hfhf`的key语句
	 * 
	 * @param key
	 *            关键字
	 * @return
	 */
	public static String getKey(String key) {
		return keyStr + key + keyStr;
	}

	/**
	 * 得到形如`hfhf`,`ewedsdswew`,`ewewew`的key语句
	 * 
	 * @param keys
	 * @return
	 */
	public static String getKeys(String[] keys) {
		String str = getKey(keys[0]);
		for (int i = 1; i <= keys.length - 1; i++) {
			str += splitStr + getKey(keys[i]);
		}
		return str;
	}

	/**
	 * 得到形如`hfhf`的value语句
	 * 
	 * @param value
	 * @return
	 */
	public static String getValue(String value) {
		return valueStr + value + valueStr;
	}

	/**
	 * 得到形如`hfhf`,`ewedsdswew`,`ewewew`的value语句
	 * 
	 * @param values
	 * @return
	 */
	public static String getValues(String[] values) {
		String str = getValue(values[0]);
		for (int i = 1; i <= values.length - 1; i++) {
			str += splitStr + getValue(values[i]);
		}
		return str;
	}

	/**
	 * 得到形如`hfhf`='ggdgd',`ewedsdswew`='7411',`ewewew`='1311'的value语句
	 * 
	 * @param value
	 * @return
	 */
	public static String getKeysAndValues(HashMap<String, String> value) {
		String[] keys = value.keySet().toArray(new String[0]);
		String tmp = keys[0];
		String str = getKey(tmp) + equalStr + getValue(value.get(tmp));
		for (int i = 1; i <= keys.length - 1; i++) {
			tmp = keys[i];
			str += splitStr + getKey(tmp) + equalStr + getValue(value.get(tmp));
		}

		return str;
	}

	/**
	 * 得到形如(`jhewew`='5611') AND (`sfwewew`='8911')的where、条件
	 * 
	 * @param where
	 * @return
	 */
	public static String getWhere(HashMap<String, String> where) {
		String[] keys = where.keySet().toArray(new String[0]);
		String tmp = keys[0];
		String str = leftBracket + getKey(tmp) + equalStr
				+ getValue(where.get(tmp)) + rightBracket;
		for (int i = 1; i <= keys.length - 1; i++) {
			tmp = keys[i];
			str += andStr + leftBracket + getKey(tmp) + equalStr
					+ getValue(where.get(tmp)) + rightBracket;
		}

		return str;
	}

	/**
	 * Executes the given SQL statement, which may be an INSERT, UPDATE, or
	 * DELETE statement or an SQL statement that returns nothing, such as an SQL
	 * DDL statement.Parameters: sql an SQL Data Manipulation Language (DML)
	 * statement, such as INSERT, UPDATE or DELETE; or an SQL statement that
	 * returns nothing, such as a DDL statement. Returns: either (1) the row
	 * count for SQL Data Manipulation Language (DML) statements or (2) 0 for
	 * SQL statements that return nothing
	 * 
	 * @param connection
	 * @param query
	 * @return
	 */
	public static int executeUpdate(Connection connection, String query) {

		int flag = -1;

		Statement stmt = null;
		try {
			stmt = connection.createStatement();
			flag = stmt.executeUpdate(query);
		} catch (SQLException e) {
			Logger logger = MyLog.getLog("Sql");
			logger.error(e.getMessage());
		}

		try {
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return flag;

	}

	/**
	 * Executes the given SQL statement, which may be an INSERT, UPDATE, or
	 * DELETE statement or an SQL statement that returns nothing, such as an SQL
	 * DDL statement.Parameters: sql an SQL Data Manipulation Language (DML)
	 * statement, such as INSERT, UPDATE or DELETE; or an SQL statement that
	 * returns nothing, such as a DDL statement. Returns: either (1) the row
	 * count for SQL Data Manipulation Language (DML) statements or (2) 0 for
	 * SQL statements that return nothing
	 * 
	 * @param stmt
	 * @param query
	 * @return
	 */
	public static int executeUpdate(Statement stmt, String query) {

		int flag = -1;

		try {
			flag = stmt.executeUpdate(query);
		} catch (SQLException e) {
			Logger logger = MyLog.getLog("Sql");
			logger.error(e.getMessage());
		}

		return flag;

	}

	/**
	 * Executes the given SQL statement, which may be an INSERT, UPDATE, or
	 * DELETE statement or an SQL statement that returns nothing, such as an SQL
	 * DDL statement.Parameters: sql an SQL Data Manipulation Language (DML)
	 * statement, such as INSERT, UPDATE or DELETE; or an SQL statement that
	 * returns nothing, such as a DDL statement. Returns: either (1) the row
	 * count for SQL Data Manipulation Language (DML) statements or (2) 0 for
	 * SQL statements that return nothing
	 * 
	 * @param query
	 * @return
	 */
	public static int executeUpdate(String query) {

		int flag = -1;

		Connection connection = DBConnect.getDBonnect().getConnection();

		if (connection == null) {
			Logger logger = MyLog.getLog("Sql");
			logger.error("连接建立失败");
			return flag;
		}

		Statement stmt = null;
		try {
			stmt = connection.createStatement();
			flag = stmt.executeUpdate(query);
		} catch (SQLException e) {
			Logger logger = MyLog.getLog("Sql");
			logger.error(e.getMessage());
		}

		DBConnect.getDBonnect().closeConnection(null, stmt, connection);

		return flag;

	}

	/**
	 * 查询数据，得到一条记录值(内含LIMIT 0, 1限定)，返回单列值，如果排序，可在query后添加order
	 * by条件，如query+"order by id desc"
	 * 
	 * @param query
	 *            查询语句，通过SqlSelect.getSelect(String table, String
	 *            column,HashMap<String, String> where)获得
	 * @return 单列值
	 */
	public static String executeQuery(String query) {
		String rs = null;

		ResultSet result = null;

		Connection connection = DBConnect.getDBonnect().getConnection();

		if (connection == null) {
			Logger logger = MyLog.getLog("Sql");
			logger.error("连接建立失败");
			return rs;
		}

		Statement stmt = null;
		try {
			stmt = connection.createStatement();
			result = stmt.executeQuery(query + singleSelect);
			if (result.next()) {
				rs = result.getString(1);
			}
		} catch (SQLException e) {
			Logger logger = MyLog.getLog("Sql");
			logger.error(e.getMessage());
		}

		DBConnect.getDBonnect().closeConnection(result, stmt, connection);

		return rs;

	}

	/**
	 * 查询数据，得到一条记录值(内含LIMIT 0, 1限定)，返回多列值，如果排序，可在query后添加order
	 * by条件，如query+"order by id desc"
	 * 
	 * @param query
	 *            查询语句，通过SqlSelect.getSelect(String table, String[]
	 *            columns,HashMap<String, String> where)获得
	 * @param i
	 *            查询列值得个数
	 * @return 多列值，通过";"分割
	 */
	public static String executeQuery(String query, int i) {
		String rs = "";

		ResultSet result = null;

		Connection connection = DBConnect.getDBonnect().getConnection();

		if (connection == null) {
			Logger logger = MyLog.getLog("Sql");
			logger.error("连接建立失败");
			return rs;
		}

		Statement stmt = null;
		try {
			stmt = connection.createStatement();
			result = stmt.executeQuery(query + singleSelect);

			if (result.next()) {
				rs = result.getString(1);
				for (int j = 1; j < i; j++) {
					rs += ";" + result.getString(j + 1);
				}
			}
		} catch (SQLException e) {
			Logger logger = MyLog.getLog("Sql");
			logger.error(e.getMessage());
		}

		DBConnect.getDBonnect().closeConnection(result, stmt, connection);

		return rs;

	}

	/**
	 * 查询数据，返回多条数据，返回多列值，如果排序，可在query后添加order by条件，如query+"order by id desc"
	 * 
	 * @param query
	 *            查询语句，通过SqlSelect.getSelect(String table, String[]
	 *            columns,HashMap<String, String> where)获得
	 * @param i
	 *            查询列值得个数
	 * @return 多列值，通过";"分割
	 */
	public static ArrayList<String> executeQuerys(String query, int i) {
		ArrayList<String> rtn = new ArrayList<String>();
		ResultSet result = null;
		String rs = "";
		Connection connection = DBConnect.getDBonnect().getConnection();

		if (connection == null) {
			Logger logger = MyLog.getLog("Sql");
			logger.error("连接建立失败");
			return rtn;
		}

		Statement stmt = null;
		try {
			stmt = connection.createStatement();
			result = stmt.executeQuery(query);

			while (result.next()) {
				rs = result.getString(1);
				for (int j = 1; j < i; j++) {
					rs += ";" + result.getString(j + 1);
				}
				rtn.add(rs);
			}
		} catch (SQLException e) {
			Logger logger = MyLog.getLog("Sql");
			logger.error(e.getMessage());
		}

		DBConnect.getDBonnect().closeConnection(result, stmt, connection);

		return rtn;

	}
}
